package cn.hsa.fqz.task.service.impl;

import cn.hsa.fqz.common.enums.DataSourceTypeEnums;
import cn.hsa.fqz.common.enums.SqlTypeEnum;
import cn.hsa.fqz.common.utils.CHExecutor;
import cn.hsa.fqz.common.utils.MySqlExecutor;
import cn.hsa.fqz.common.utils.OdpsExecutor;
import cn.hsa.fqz.common.utils.page.BasePage;
import cn.hsa.fqz.common.utils.page.PageData;
import cn.hsa.fqz.task.controller.vo.ClientDsConfigVO;
import cn.hsa.fqz.task.dao.SysDsConfigMapper;
import cn.hsa.fqz.task.entity.SysDsConfig;
import cn.hsa.fqz.task.service.SqlClientService;
import cn.hsa.fqz.task.service.dto.SqlExecuteDTO;
import cn.hsa.fqz.task.service.dto.SqlTranslateResultDTO;
import cn.hsa.fqz.util.TimestampStringConverter;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.OutputStream;
import java.util.*;

/**
 * @author Administrator
 * @version 1.0
 * @date 2023-11-16 15:04
 */
@Slf4j
@Service
public class SqlClientServiceImpl implements SqlClientService {

    @Autowired
    private SysDsConfigMapper sysDsConfigMapper;

    @Override
    public List<SysDsConfig> getAllDsConfig() {
        return sysDsConfigMapper.selectAll();
    }

    @Override
    public Object startSqlExecute(SqlExecuteDTO execute) {
        String dbCode = execute.getDbCode();
        SysDsConfig config = sysDsConfigMapper.selectByDBCode(dbCode);
        List<String> sqlList = execute.getExecuteSqls();
        BasePage page = execute.getPage();
        List<Object> result = Lists.newArrayList();
        for (String sql:sqlList) {
            if(sql.toUpperCase().startsWith(SqlTypeEnum.SELECT.toString())){
                if(config.getDbType().equals(DataSourceTypeEnums.CLICKHOUSE.toString())){
                    PageData<Map<String, Object>> pageData = CHExecutor.getMapPage(config.getDbHost(), config.getDbPort(), config.getDbPassword(), sql, page.getPageNum(), page.getPageSize());
                    result.add(pageData);
                }else if(config.getDbType().equals(DataSourceTypeEnums.ODPS.toString())){
                    PageData<Map<String, Object>> pageData = OdpsExecutor.getMapPage(config.getDbHost(), config.getProjectName(),config.getDbUsername(),config.getDbPassword(), sql, page.getPageNum(), page.getPageSize());
                    result.add(pageData);
                }else{
                    PageData<Map<String, Object>> pageData = MySqlExecutor.getMapPage(config.getDbHost(), config.getDbPort(),config.getDbsName(),config.getDbUsername(),config.getDbPassword(), sql, page.getPageNum(), page.getPageSize());
                    result.add(pageData);
                }
            }else{
                String startTime = DateUtil.formatDateTime(new Date());
                if(config.getDbType().equals(DataSourceTypeEnums.CLICKHOUSE.toString())){
                    SqlTranslateResultDTO dto = new SqlTranslateResultDTO();
                    dto.setStartTime(startTime);
                    dto.setQuerySql(sql);
                    long rows = CHExecutor.executeSql(config.getDbHost(), config.getDbPort(), config.getDbPassword(), sql);
                    dto.setRows(rows);
                    String endTime = DateUtil.formatDateTime(new Date());
                    dto.setEndTime(endTime);
                    result.add(dto);
                }else{
                    SqlTranslateResultDTO dto = new SqlTranslateResultDTO();
                    dto.setStartTime(startTime);
                    dto.setQuerySql(sql);
                    long rows = MySqlExecutor.executeSql(config.getDbHost(), config.getDbPort(),config.getDbsName(),config.getDbUsername(),config.getDbPassword(), sql);
                    dto.setRows(rows);
                    String endTime = DateUtil.formatDateTime(new Date());
                    dto.setEndTime(endTime);
                    result.add(dto);
                }
            }
        }
        return result;
    }

    @Override
    public Integer addDs(ClientDsConfigVO vo) {
        SysDsConfig config = new SysDsConfig();
        BeanUtils.copyProperties(vo,config);
        return sysDsConfigMapper.insertSelective(config);
    }

    @Override
    public Integer updateDs(ClientDsConfigVO vo) {
        SysDsConfig config = new SysDsConfig();
        BeanUtils.copyProperties(vo,config);
        return sysDsConfigMapper.updateByPrimaryKeySelective(config);
    }

    @Override
    public Integer deleteDs(Integer dsId) {
        return sysDsConfigMapper.deleteByPrimaryKey(dsId);
    }

    @Override
    public void startSqlExecuteAndExport(SqlExecuteDTO execute, OutputStream os) {
        String dbCode = execute.getDbCode();
        SysDsConfig config = sysDsConfigMapper.selectByDBCode(dbCode);
        List<String> sqlList = execute.getExecuteSqls();
        BasePage page = execute.getPage();
        int i=0;
        for (String sql:sqlList) {
            i++;
            if(sql.toUpperCase().startsWith(SqlTypeEnum.SELECT.toString())){
                if(config.getDbType().equals(DataSourceTypeEnums.CLICKHOUSE.toString())){
                    List<Map<String, Object>> list = CHExecutor.getMapList(config.getDbHost(), config.getDbPort(), config.getDbPassword(), sql);
                    List<String> headls = Lists.newArrayList();
                    Set<String> set = list.get(0).keySet();
                    for(String col: set){
                        headls.add(col);
                    }
                    List<List<String>> headList = Lists.newArrayList();
                    for (String s:headls) {
                        headList.add(Arrays.asList(s));
                    }
                    List<List<Object>> xlsData = genXlsData(headls, list);
                    EasyExcel.write(os).registerConverter(new TimestampStringConverter()).head(headList).sheet(i).doWrite(xlsData);
                }else if(config.getDbType().equals(DataSourceTypeEnums.ODPS.toString())){
                    List<Map<String, Object>> list = OdpsExecutor.getMapList(config.getDbHost(), config.getProjectName(),config.getDbUsername(),config.getDbPassword(), sql);
                    List<String> headls = Lists.newArrayList();
                    Set<String> set = list.get(0).keySet();
                    for(String col: set){
                        headls.add(col);
                    }
                    List<List<String>> headList = Lists.newArrayList();
                    for (String s:headls) {
                        headList.add(Arrays.asList(s));
                    }
                    List<List<Object>> xlsData = genXlsData(headls, list);
                    EasyExcel.write(os).registerConverter(new TimestampStringConverter()).head(headList).sheet(i).doWrite(xlsData);
                }else{
                    List<Map<String, Object>> list = MySqlExecutor.getMapList(config.getDbHost(), config.getDbPort(),config.getDbsName(),config.getDbUsername(),config.getDbPassword(), sql);
                    List<String> headls = Lists.newArrayList();
                    Set<String> set = list.get(0).keySet();
                    for(String col: set){
                        headls.add(col);
                    }
                    List<List<String>> headList = Lists.newArrayList();
                    for (String s:headls) {
                        headList.add(Arrays.asList(s));
                    }
                    List<List<Object>> xlsData = genXlsData(headls, list);
                    EasyExcel.write(os).registerConverter(new TimestampStringConverter()).head(headList).sheet(i).doWrite(xlsData);
                }
            }
        }

    }

    private List<List<Object>> genXlsData(List<String> titles,List<Map<String, Object>> data){
        List<List<Object>> result = Lists.newArrayList();
        for (Map<String,Object> row:data) {
            List<Object> rowDatas = Lists.newArrayList();
            for(String title:titles){
                rowDatas.add(row.get(title));
            }
            result.add(rowDatas);
        }
        return result;
    }
}
